package org.example.managementsystem.repository;

import org.example.managementsystem.model.Product;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.List;
import java.util.Objects;

/**
 * 产品数据访问层
 */
@Repository
public class ProductDao {
    private final JdbcTemplate jdbcTemplate;

    @Autowired
    public ProductDao(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    /**
     * 更新产品身份码
     */
    public int updateProductCode(Long id, String productCode) {
        String sql = "UPDATE product SET product_code = ? WHERE id = ?";
        return jdbcTemplate.update(sql, productCode, id);
    }

    /**
     * 批量更新空产品身份码
     * 根据加工车间自动生成产品身份码
     */
    public int batchUpdateEmptyProductCodes() {
        String sql = "UPDATE product SET product_code = " +
                "CASE process_department " +
                "WHEN '表面处理车间' THEN CONCAT('BM', id) " +
                "WHEN '锻压车间' THEN CONCAT('DY', id) " +
                "WHEN '组装车间' THEN CONCAT('ZZ', id) " +
                "WHEN '质检车间' THEN CONCAT('ZJ', id) " +
                "WHEN '机加车间' THEN CONCAT('JJ', id) " +
                "ELSE CONCAT('QT', id) " +
                "END " +
                "WHERE product_code IS NULL OR product_code = ''";
        
        return jdbcTemplate.update(sql);
    }

    /**
     * 添加产品
     */
    public Long insertProduct(Product product) {
        String sql = "INSERT INTO product (product_code, product_name, appearance_size, batch, operator, " +
                "process_method, process_time, process_department) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        
        KeyHolder keyHolder = new GeneratedKeyHolder();
        
        jdbcTemplate.update(connection -> {
            PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, product.getProductCode());
            ps.setString(2, product.getName());
            ps.setString(3, product.getSize());
            ps.setString(4, product.getBatch());
            ps.setString(5, product.getOperatorName());
            ps.setString(6, product.getProcessMethod());
            ps.setTimestamp(7, product.getProcessTime() != null ? 
                    Timestamp.valueOf(product.getProcessTime()) : null);
            ps.setString(8, product.getWorkshop());
            return ps;
        }, keyHolder);
        
        return Objects.requireNonNull(keyHolder.getKey()).longValue();
    }

    /**
     * 更新产品
     */
    public int updateProduct(Product product) {
        String sql = "UPDATE product SET product_code = ?, product_name = ?, appearance_size = ?, batch = ?, " +
                "operator = ?, process_method = ?, process_time = ?, process_department = ? WHERE id = ?";
        
        return jdbcTemplate.update(sql, 
                product.getProductCode(),
                product.getName(),
                product.getSize(),
                product.getBatch(),
                product.getOperatorName(),  // 确保这里使用的是operatorName字段
                product.getProcessMethod(),
                product.getProcessTime() != null ? Timestamp.valueOf(product.getProcessTime()) : null,
                product.getWorkshop(),
                product.getId());
    }

    /**
     * 删除产品
     */
    public int deleteProduct(Long id) {
        String sql = "DELETE FROM product WHERE id = ?";
        return jdbcTemplate.update(sql, id);
    }

    /**
     * 根据ID查询产品
     */
    public Product getProductById(Long id) {
        String sql = "SELECT * FROM product WHERE id = ?";
        
        List<Product> products = jdbcTemplate.query(sql, new ProductRowMapper(), id);
        return products.isEmpty() ? null : products.get(0);
    }

    /**
     * 查询所有产品
     */
    public List<Product> getAllProducts() {
        String sql = "SELECT * FROM product";
        
        return jdbcTemplate.query(sql, new ProductRowMapper());
    }

    /**
     * 根据条件查询产品
     */
    public List<Product> searchProducts(String keyword) {
        String sql = "SELECT * FROM product " +
                "WHERE product_code LIKE ? OR product_name LIKE ? OR batch LIKE ?";
        
        String searchParam = "%" + keyword + "%";
        return jdbcTemplate.query(sql, new ProductRowMapper(), searchParam, searchParam, searchParam);
    }

    /**
     * 产品行映射器
     */
    private static class ProductRowMapper implements RowMapper<Product> {
        @Override
        public Product mapRow(ResultSet rs, int rowNum) throws SQLException {
            Product product = new Product();
            product.setId(rs.getLong("id"));
            product.setProductCode(rs.getString("product_code"));
            product.setName(rs.getString("product_name"));
            product.setSize(rs.getString("appearance_size"));
            product.setBatch(rs.getString("batch"));
            product.setOperatorName(rs.getString("operator"));
            product.setProcessMethod(rs.getString("process_method"));
            
            Timestamp timestamp = rs.getTimestamp("process_time");
            if (timestamp != null) {
                product.setProcessTime(timestamp.toLocalDateTime());
            }
            
            product.setWorkshop(rs.getString("process_department"));
            
            return product;
        }
    }
}